package com.imonsoft.sqm.service.excel.SupplierDataExcel;

import java.io.File;
import java.io.FileOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.imonsoft.common.constant.ConstantDict;
import com.imonsoft.common.constant.ConstantSqm;
import com.imonsoft.common.exception.SystemException;
import com.imonsoft.common.util.PDFUtil;
import com.imonsoft.common.util.SendMail;
import com.imonsoft.common.util.StringUtil;
import com.imonsoft.sqm.pojo.basedata.Supplier;
import com.imonsoft.sqm.service.basedata.DictionaryService;
import com.imonsoft.sqm.service.basedata.SupplierService;
import com.imonsoft.sqm.service.business.supplier_quality.SupplierDataService;
import com.imonsoft.sqm.service.excel.BaseExcel.ExcelService;
import com.imonsoft.sqm.vo.business.supplier_quality.SupplierDataVo;

@Service("supplierDataExcelService")
public class SupplierDataExcelServiceImpl implements SupplierDataExcelService {
	@Autowired
	private SupplierDataService supplierDataService;
	@Autowired
	private ExcelService excelService;
	@Autowired
	private DictionaryService dictionaryService;
	@Autowired
	private SupplierService supplierService;
	@Override
	/**
	 * 通过模板写出ECCEL
	 * 
	 * @param request
	 * @param ids
	 * @param response
	 */
	public void txExportExcel(HttpServletRequest request, SupplierDataVo vo,
			HttpServletResponse response) {
		createExcel(request, vo);
		// 关闭文件输出流
		excelService.loadExcelModel(request, response, ConstantSqm.EXCEL_OUT_SUPPLIER_DATA);
	}
	public void deleteExcelAndPdf(HttpServletRequest request) {
		// 删除如果之前未删除，则删除pdf
		File everFilePdf = new File(request.getSession().getServletContext()
				.getRealPath("/excelModel/")
				+ "/" + ConstantSqm.PDF_OUT_SUPPLIER_DATA);
		if (everFilePdf.exists()) {
			everFilePdf.delete();
		}
	}
	public void txExportPdf(HttpServletRequest request, SupplierDataVo vo,
			HttpServletResponse response) {
		deleteExcelAndPdf(request);
		createExcel(request, vo);
		convertExcelToPdf(
				request.getSession().getServletContext()
						.getRealPath("/excelModel/")
						+ "/" + ConstantSqm.EXCEL_OUT_SUPPLIER_DATA,
				request.getSession().getServletContext()
						.getRealPath("/excelModel/")
						+ "/" + ConstantSqm.PDF_OUT_SUPPLIER_DATA);
	}
	public void convertExcelToPdf(String filePath, String outFile) {
		PDFUtil pu = new PDFUtil();
		pu.excelToPDF(filePath, outFile);
	}
	public void createExcel(HttpServletRequest request, SupplierDataVo vo){
		try {
			File tFile = new File(request.getSession().getServletContext()
					.getRealPath("/excelModel/")
					+ "/" + ConstantSqm.EXCEL_SUPPLIER_DATA);// 创建模板文件对象
			File outFile = new File(request.getSession().getServletContext()
					.getRealPath("/excelModel/")
					+ "/" + ConstantSqm.EXCEL_OUT_SUPPLIER_DATA);// 创建输出文件对象
			// 创建文件输出流对象
			FileOutputStream os = new FileOutputStream(outFile);
			// 模板工作簿对象
			Workbook tBook = Workbook.getWorkbook(tFile);
			// 输出工作簿对象
			WritableWorkbook wbook = Workbook.createWorkbook(os, tBook);
			WritableSheet sheet = wbook.getSheet(0);
			sheet.setName("供应商质量绩效表现");
			WritableCellFormat cellFormat = new WritableCellFormat();
			cellFormat.setBorder(Border.ALL, BorderLineStyle.THIN,jxl.format.Colour.GREEN);
			// 设置对齐方式
			cellFormat
					.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
			cellFormat.setAlignment(jxl.format.Alignment.CENTRE);
			// 设置字体
			WritableFont bodyFont = new WritableFont(WritableFont.ARIAL, 10);
			cellFormat.setFont(bodyFont);
			// 第x列，第y行
			SupplierDataVo supplierDataVo=supplierDataService.getDataGridVoByList(vo);
			if(supplierDataVo!=null){
				
				//设置字体
				    WritableFont font = new WritableFont(WritableFont.ARIAL,36,WritableFont.NO_BOLD,false,UnderlineStyle.NO_UNDERLINE,Colour.BLACK); 
				    WritableCellFormat cFormat = new WritableCellFormat(font); 
				    cFormat.setBorder(Border.ALL, BorderLineStyle.THIN,jxl.format.Colour.GREEN);
				    cFormat.setBackground(Colour.BLACK); 
				    cFormat.setAlignment(Alignment.RIGHT);
				Label label_reasonTop = new Label(1, 0,
						vo.getYear()+"年"+vo.getMonth()+"月", cFormat);
				sheet.addCell(label_reasonTop);//标题
				
				Label label_reason1 = new Label(3, 5,
						vo.getMonth(), cellFormat);
				sheet.addCell(label_reason1);//月
				Label label_reason2 = new Label(5, 5,
						vo.getYear(), cellFormat);
				sheet.addCell(label_reason2);//年
				Label label_reason3 = new Label(6, 7,
						"c", cellFormat);
				sheet.addCell(label_reason3);//等级
				Label label_reason4 = new Label(2, 10,
						vo.getSupplierCode(), cellFormat);
				sheet.addCell(label_reason4);//供应商代码
				Label label_reason5 = new Label(2, 11,
						vo.getSupplierName(), cellFormat);
				sheet.addCell(label_reason5);//供应商名称
				Label label_reasonType = new Label(2, 12,
						"", cellFormat);
				sheet.addCell(label_reasonType);//供应商类型
				
				
//				Dictionary dictionary=dictionaryService.getDictionaryCodeType("", vo.getSupplierType());
//				Label label_reason6 = new Label(6, 7,
//						vo.getSupplierType(), cellFormat);
//				sheet.addCell(label_reason6);//供应商类型
				
				Label label_reason7 = new Label(2, 16,
						StringUtil.fixup(100), cellFormat);
				sheet.addCell(label_reason7);//总分
				Label label_reason8 = new Label(5, 16,
						vo.getEvaluationValue(), cellFormat);
				sheet.addCell(label_reason8);//实际得分
				
				Label label_reason9 = new Label(2, 17,
						StringUtil.fixup(10), cellFormat);
				sheet.addCell(label_reason9);//质量体系
				Label label_reason10 = new Label(5, 17,
						vo.getQmSystem(), cellFormat);
				sheet.addCell(label_reason10);//实际得分
				
				Label label_reason11 = new Label(2, 18,
						StringUtil.fixup(10), cellFormat);
				sheet.addCell(label_reason11);//TS16949 认证
				Label label_reason12 = new Label(5, 18,
						"", cellFormat);
				sheet.addCell(label_reason12);//实际得分
				
				Label label_reason13 = new Label(2, 19,
						StringUtil.fixup(10), cellFormat);
				sheet.addCell(label_reason13);//DIN ISO 9001 认证
				Label label_reason14 = new Label(5, 19,
						"", cellFormat);
				sheet.addCell(label_reason14);//实际得分
				
				Label label_reason15 = new Label(2, 20,
						StringUtil.fixup("k.o"), cellFormat);
				sheet.addCell(label_reason15);//低于DIN ISO 9001的认证，供应商不允许交付到上海爱德夏
				Label label_reason16 = new Label(5, 20,
						"", cellFormat);
				sheet.addCell(label_reason16);//实际得分
				
				Label label_reason17 = new Label(2, 21,
						StringUtil.fixup("50"), cellFormat);
				sheet.addCell(label_reason17);//过程审核(多次审核的得分总和/审核次数的二倍）
				Label label_reason18 = new Label(5, 21,
						vo.getProcessAudits(), cellFormat);
				sheet.addCell(label_reason18);//实际得分
				
				Label label_reason19 = new Label(2, 22,
						StringUtil.fixup("50"), cellFormat);
				sheet.addCell(label_reason19);//基于VDA6.3、AQP或者等同标准的审核
				Label label_reason20 = new Label(5, 22,
						"", cellFormat);
				sheet.addCell(label_reason20);//实际得分
				
				Label label_reason21 = new Label(2, 23,
						StringUtil.fixup("50"), cellFormat);
				sheet.addCell(label_reason21);//自评（没有审核）
				Label label_reason22 = new Label(5, 23,
						"", cellFormat);
				sheet.addCell(label_reason22);//实际得分
				
				Label label_reason23 = new Label(2, 24,
						StringUtil.fixup("35"), cellFormat);
				sheet.addCell(label_reason23);//0公里交付质量
				Label label_reason24 = new Label(5, 24,
						vo.getMilesDeliveryQuality(), cellFormat);
				sheet.addCell(label_reason24);//实际得分
				
				Label label_reason25 = new Label(2, 25,
						StringUtil.fixup("35"), cellFormat);
				sheet.addCell(label_reason25);//质量风险系数小于目标值
				Label label_reason26 = new Label(5, 25,
						"", cellFormat);
				sheet.addCell(label_reason26);//实际得分
				
				Label label_reason27 = new Label(2, 26,
						StringUtil.fixup("30"), cellFormat);
				sheet.addCell(label_reason27);//目标值<质量风险系数<1.25倍目标值
				Label label_reason28 = new Label(5, 26,
						"", cellFormat);
				sheet.addCell(label_reason28);//实际得分
				
				Label label_reason29 = new Label(2, 27,
						StringUtil.fixup("20"), cellFormat);
				sheet.addCell(label_reason29);//1.25目标值<质量风险系数<1.5倍目标值
				Label label_reason30 = new Label(5, 27,
						"", cellFormat);
				sheet.addCell(label_reason30);//实际得分
				
				Label label_reason31 = new Label(2, 28,
						StringUtil.fixup("10"), cellFormat);
				sheet.addCell(label_reason31);//1.50目标值<质量风险系数<1.75倍目标值
				Label label_reason32 = new Label(5, 28,
						"", cellFormat);
				sheet.addCell(label_reason32);//实际得分
				
				Label label_reason33 = new Label(2, 29,
						StringUtil.fixup("0"), cellFormat);
				sheet.addCell(label_reason33);//质量风险系数 >1.75倍目标值
				Label label_reason34 = new Label(5, 29,
						"", cellFormat);
				sheet.addCell(label_reason34);//实际得分
				
				Label label_reason35 = new Label(2, 30,
						StringUtil.fixup("5"), cellFormat);
				sheet.addCell(label_reason35);//问题响应速度
				Label label_reason36 = new Label(5, 30,
						vo.getResponseOnProblems(), cellFormat);
				sheet.addCell(label_reason36);//实际得分
				
				Label label_reason37 = new Label(2, 31,
						StringUtil.fixup("5"), cellFormat);
				sheet.addCell(label_reason37);//好，十分快
				Label label_reason38 = new Label(5, 31,
						"", cellFormat);
				sheet.addCell(label_reason38);//实际得分
				
				Label label_reason39 = new Label(2, 32,
						StringUtil.fixup("3"), cellFormat);
				sheet.addCell(label_reason39);//满意
				Label label_reason40 = new Label(5, 32,
						"", cellFormat);
				sheet.addCell(label_reason40);//实际得分
				
				Label label_reason41 = new Label(2, 33,
						StringUtil.fixup("0"), cellFormat);
				sheet.addCell(label_reason41);//不好
				Label label_reason42 = new Label(5, 33,
						"", cellFormat);
				sheet.addCell(label_reason42);//实际得分
				
				Label label_reason43 = new Label(2, 34,
						StringUtil.fixup("动态variable"), cellFormat);
				sheet.addCell(label_reason43);//特殊抱怨
				Label label_reason44 = new Label(5, 34,
						vo.getDeductiblesForSpecialIncidents(), cellFormat);
				sheet.addCell(label_reason44);//实际得分
				
				Label label_reason45 = new Label(2, 35,
						StringUtil.fixup("-2"), cellFormat);
				sheet.addCell(label_reason45);//在顾客处发生抱怨产生的筛选和返工（每个投诉）
				Label label_reason46 = new Label(5, 35,
						"", cellFormat);
				sheet.addCell(label_reason46);//实际得分
				
				Label label_reason47 = new Label(2, 36,
						StringUtil.fixup("-5"), cellFormat);
				sheet.addCell(label_reason47);//一级受控发运（每次）
				Label label_reason48 = new Label(5, 36,
						"", cellFormat);
				sheet.addCell(label_reason48);//实际得分
				
				Label label_reason49 = new Label(2, 37,
						StringUtil.fixup("-7"), cellFormat);
				sheet.addCell(label_reason49);//二级受控发运（每次）
				Label label_reason50 = new Label(5, 37,
						"", cellFormat);
				sheet.addCell(label_reason50);//实际得分
				
				Label label_reason51 = new Label(2, 38,
						StringUtil.fixup("-10"), cellFormat);
				sheet.addCell(label_reason51);//擅自更改过程和产品（每个投诉）
				Label label_reason52 = new Label(5, 38,
						"", cellFormat);
				sheet.addCell(label_reason52);//实际得分
				
				Label label_reason53 = new Label(2, 39,
						StringUtil.fixup("动态variable"), cellFormat);
				sheet.addCell(label_reason53);//环境管理体系
				Label label_reason54 = new Label(5, 39,
						vo.getEnvironmentalManagementSystem(), cellFormat);
				sheet.addCell(label_reason54);//实际得分
				
				Label label_reason55 = new Label(2, 40,
						StringUtil.fixup("-2"), cellFormat);
				sheet.addCell(label_reason55);//没有ISO 14001 or EMAS 的环境体系认证但是有证据计划两年内完成
				Label label_reason56 = new Label(5, 40,
						"", cellFormat);
				sheet.addCell(label_reason56);//实际得分
				
				Label label_reason57 = new Label(2, 41,
						StringUtil.fixup("-2"), cellFormat);
				sheet.addCell(label_reason57);//没有ISO 14001 or EMAS 的环境体系认证，也没有证据计划两年内完成，必须针对此体系要求自审
				Label label_reason58 = new Label(5, 41,
						"", cellFormat);
				sheet.addCell(label_reason58);//实际得分
				
			}
			wbook.write();
			wbook.close();
			tBook.close();
			os.close();
		} catch (Exception e) {
			System.out.println(e.getMessage());
			e.printStackTrace();
		}
	}
	/**
	 * 供应商数据发送邮件
	 * @param qualitySystem
	 * @param user
	 * @param request
	 */
	@Override
	public void txSendMail(HttpServletRequest request,HttpServletResponse response,SupplierDataVo vo){
		//************************四个参数为系统默认邮箱参数，如需修改请到数据字典维护***************************
		// 邮箱服务器名称
		String servicename = dictionaryService.getNameByCodeType(ConstantDict.YXFWQMC, ConstantDict.MAIL);
		// 发送人邮箱名称
		String userEmail = dictionaryService.getNameByCodeType(ConstantDict.FSRYXMC, ConstantDict.MAIL);
		// 邮箱发送密码
		String password = dictionaryService.getNameByCodeType(ConstantDict.FSRYXMM, ConstantDict.MAIL);
		// 邮箱发送人名称
		//String username = dictionaryService.getNameByCodeType(ConstantDict.YXFSRMC, ConstantDict.MAIL);
		String title="供应商数据";
		txExportPdf(request, vo, response);
		Supplier supplier = supplierService.get(Long.valueOf(vo.getSupplierId()));
		if(supplier==null){
			throw new SystemException("用户数据位空，请检查供应商数据");
		}
		String mailbody=supplier.getName()+"</br>您好：</br>贵公司的供应商数据，请见附件";
		SendMail.sendMail(mailbody, servicename, userEmail, password, title, supplier.getMail(), request.getSession().getServletContext()
				.getRealPath("/excelModel/")
				+ "/"+ConstantSqm.PDF_OUT_SUPPLIER_DATA);
		deleteExcelAndPdf(request);
	}
}
